﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;

namespace DAL
{
    public class GoodsDAL
    {
        //根据搜索关键字查询数据
        public static List<Goods> getBykeyDAL(string key)
        {
            string sql = string.Format("SELECT g.GoodsID,g.GoodsName,g.GoodsPic,g.GoodsPrice,g.GoodsDesc,g.tyID,g.AddTime,g.GoodsState FROM tb_GoodsClass s,tb_Goods g WHERE  s.tyID=g.tyID and  ClassName like '%{0}%' and GoodsState=0", key);
            List<Goods> list = new List<Goods>();
            SqlDataReader dr=DBHelper.GetDataReader(sql);
            while (dr.Read())
            {
                Goods goods=new Goods();
                goods.GoodsID = dr.GetInt32(0);
                goods.GoodsName = dr.GetString(1);
                goods.GoodsPic = dr.GetString(2);
                goods.GoodsPrice = dr.GetDecimal(3);
                goods.GoodsDesc = dr.GetString(4);
                goods.tyID = dr.GetInt32(5);
                goods.AddTime = dr.GetDateTime(6);
                goods.GoodsState = dr.GetInt32(7);
                list.Add(goods);
            }
            dr.Close();
            return list;
        }
        //根据类型id查询数据
        public static List<Goods> getBytyIdDAL(int tyID)
        {
            string sql = string.Format("select * from tb_Goods where tyID={0}", tyID);
            List<Goods> list = new List<Goods>();
            SqlDataReader dr=DBHelper.GetDataReader(sql);
            while(dr.Read())
            {
                Goods goods = new Goods();
                goods.GoodsID = dr.GetInt32(0);
                goods.GoodsName=dr.GetString(1);
                goods.GoodsPic=dr.GetString(2);
                goods.GoodsPrice=dr.GetDecimal(3);
                goods.GoodsDesc=dr.GetString(4);
                goods.tyID=dr.GetInt32(5);
                goods.AddTime=dr.GetDateTime(6);
                goods.GoodsState=dr.GetInt32(7);
                list.Add(goods);
            }
            dr.Close();
            return list;
        }
        //根据商品ID查询单个信息
        public static Goods getByIDDAL(int GoodsID)
        {
            string sql=string.Format("select * from tb_Goods where GoodsID={0}",GoodsID);
            Goods goods = null;
            SqlDataReader dr=DBHelper.GetDataReader(sql);
            if(dr.Read())
            {
                goods = new Goods();
                goods.GoodsID = dr.GetInt32(0);
                goods.GoodsName = dr.GetString(1);
                goods.GoodsPic = dr.GetString(2);
                goods.GoodsPrice = dr.GetDecimal(3);
                goods.GoodsDesc = dr.GetString(4);
                goods.tyID = dr.GetInt32(5);
                goods.AddTime = dr.GetDateTime(6);
                goods.GoodsState = dr.GetInt32(7);
            }
            dr.Close ();    
            return goods;
        }
        //获取前十条数据
        public static List<Goods> getAllDAl()
        {
            string sql = string.Format("select  top 10 * from tb_Goods");
            List<Goods> list = new List<Goods>();
            SqlDataReader dr = DBHelper.GetDataReader(sql);
            while (dr.Read())
            {
                Goods goods = new Goods();
                goods.GoodsID = dr.GetInt32(0);
                goods.GoodsName = dr.GetString(1);
                goods.GoodsPic = dr.GetString(2);
                goods.GoodsPrice = dr.GetDecimal(3);
                goods.GoodsDesc = dr.GetString(4);
                goods.tyID = dr.GetInt32(5);
                goods.AddTime = dr.GetDateTime(6);
                goods.GoodsState = dr.GetInt32(7);
                list.Add(goods);
            }
            dr.Close();
            return list;
        }
    }
}
